"""
#!/usr/bin/python3
# coding:utf-8            
#
# Copyright (C) 2024 - 2024 Jasonakeke， Inc. All Rights Reserved 
# @Desc	   ：
# @Time    : 2024/8/17 21:27
# @Author  : Code_By_Jasonakeke
# @Email   : 2284037977@qq.com
# @File    : 06-ORM中的一对一关联.py
# @IDE     : PyCharm
"""
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, relationship, sessionmaker, backref

# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

# 创建数据库引擎
engine = create_engine(DB_URI)

# 第一步：创建一个基类（所有 ORM 中的 O，对象模型的超级父类）
Base = declarative_base()

# 第一种写法：把 uselist = False 加在没有外键的对象中，其他和前面的移动到关联是一样的
# 第二种写法：在主表中不需要维护关联关系（不用加关联属性），只要在从表对象中加上关联属性，并且加 backref
class Person(Base):
	__tablename__ = 't_person'
	id = Column(name = 'id', type_ = Integer, primary_key = True, autoincrement = True)
	name = Column(name = 'name', type_ = String(255))
	age = Column(name = 'age', type_ = Integer)
	address = Column(type_ = String(255))
	# 创建表之后新加的
	country = Column(type_ = String(50))
	city = Column(type_ = String(50))

	# id_card = relationship('IdCard', uselist = False)

	def __str__(self):
		return f'Person(id={self.id}, name={self.name}, age={self.age}, address={self.address}, country={self.country}, city={self.city})'


class IdCard(Base):
	__tablename__ = 't_id_card'
	card_number = Column(type_ = String(18), primary_key = True)
	p_id = Column(Integer, ForeignKey('t_person.id'))

	person = relationship('Person', backref = backref('id_card', uselist = False))

	def __str__(self):
		return f'IdCard(card_number={self.card_number}, p_id={self.p_id})'


# Base.metadata.drop_all(engine)
# Base.metadata.create_all(engine)

session = sessionmaker(bind = engine)()
def save():
	c1 = IdCard(card_number = '1000001', p_id = 1)
	c2 = IdCard(card_number = '1000002', p_id = 2)
	c3 = IdCard(card_number = '1000003', p_id = 3)
	session.add_all([c1, c2, c3])
	session.commit()

def query():
	p = session.query(Person).filter(Person.id == 1).first()
	print(p)
	print(p.id_card.card_number)

	card = session.query(IdCard).filter(IdCard.card_number == '1000002').first()
	print(card)
	print(card.person.name)



def update():
	# 把身份证号码为 1000002，改成是刘六的身份证
	card = session.query(IdCard).filter(IdCard.card_number == '1000002').first()
	p = session.query(Person).filter(Person.name == '刘六').first()
	card.p_id = p.id
	# card.person = p
	session.commit()


if __name__ == '__main__':
	update()